package zuoye882;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import zuoye.DBUtil;

public class ExamresultDao {
	public int add(Examresult a) {
		Connection con = DBUtil.getConnection();
		String sql = "insert into examresult values(?,?,?,?,?)";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, a.getId());
			st.setDate(2, new java.sql.Date(a.getDate().getTime()));
			st.setInt(3, a.getScore());
			st.setInt(4, a.getSb_id());
			st.setInt(5, a.getSt_id());
			int b = st.executeUpdate();
			return b;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(st);
			DBUtil.close(con);
		}
		return 0;
	}

	public int update(int id, int score) {
		Connection con = DBUtil.getConnection();
		String sql = "update examresult set score=? where id=?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, score);
			st.setInt(2, id);
			int b = st.executeUpdate();
			return b;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public int delet(int id) {
		Connection con = DBUtil.getConnection();
		String sql = "delete from examresult where id=?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			int b = st.executeUpdate();
			return b;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public List<Examresult> findAll(int pagesize, int pagenum) {
		int start = (pagenum - 1) * pagesize;
		int end = pagesize * pagenum;
		Connection con = DBUtil.getConnection();
		String sql = "select * from examresult";
		PreparedStatement st = null;
		List<Examresult> a = new ArrayList<Examresult>();
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			st.setMaxRows(end);
			rs = st.executeQuery();
			if (start > 0) {
				rs.absolute(start);
			}
			while (rs.next()) {
				Examresult b = new Examresult();
				b.setId(rs.getInt("id"));
				b.setDate(rs.getDate("exam_date"));
				b.setScore(rs.getInt("score"));
				b.setSb_id(rs.getInt("sb_id"));
				b.setSt_id(rs.getInt("st_id"));
				a.add(b);
			}
			return a;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
